/*
 * StoreProductDAO.java
 *
 * Created on March 5, 2007, 4:18 PM
 * 
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.jjenkins.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

 /**
 * This class contains methods to create, read, update and delete the 
 * storeproduct Object. It also contains methods to read an object by
 * serveral different criteria. All interaction between the object 
 * and the database is done through this class. 
 * 
 * @author      Jeff Jenkins
 * @version     1.0
 * 
 */
public class StoreProductDAO {
    
    private static StoreProductDAO instance = null;
    
    /** Creates a new instance of StoreProductDAO */
    private StoreProductDAO() {
    }
    
        /* This method implements the singleton pattern. It insures that only
         * on instance of StoreProductDAO exists
         *
         * @return: StoreProductDAO instance */
    
    public static synchronized StoreProductDAO getInstance() {
        if (instance == null) {
            instance = new StoreProductDAO();
        }
        return instance;
    }
    
    
    /* This method creates a new StoreProductBO, creates and sets the GUID, and
     * returns it to the calling method.
     *
     * @return: StoreProductBO
     * @throws: DataException
     *
     */
    
    public StoreProductBO create() throws DataException {
        try {
            String id = GUID.generate();
            StoreProductBO storeproduct = new StoreProductBO();
            storeproduct.setId(id);
            Cache.getInstance().put(id, storeproduct);
            return storeproduct;
            
            
        } catch (Exception e) {
            throw new DataException("Could not create GUID", e);
        }
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     *
     * @Par: String: id
     * @Return: StoreProductBO
     * @Throws: DataException
     */
    public synchronized StoreProductBO read(String id) throws DataException {
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        StoreProductBO storeproduct = (StoreProductBO)cache.get(id);
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // If the item is not in cache a connection is checked out and
            // The item is read from the database.
            if(storeproduct == null){
                try {
                    //reads in customer
                    storeproduct = read(id, conn);
                    
                }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
                    
                }
            }
            
            // After all items are read the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        
        // Return Object
        return storeproduct;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  @Par: String: id, Connection conn
     *  @Return: StoreProductBO
     *  @throws: DataException, SQLException
     */
    synchronized StoreProductBO read(String id, Connection conn) throws SQLException, DataException {
        
        
        // Checks to see if the item is in cache
        
        Cache cache = Cache.getInstance();
        StoreProductBO storeproduct = (StoreProductBO)cache.get(id);
        
        // Quieries that database
        if(storeproduct == null){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM store_product WHERE storeproduct_id = ?");
            pstmt.setString(1, id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            if (rs.next()) {
                storeproduct = new StoreProductBO();
                storeproduct.setId(rs.getString("product_id"));//what is the PK of a SP?
                storeproduct.setQtyOnHand(rs.getInt("qtyonhand"));
                storeproduct.setShelfLocation(rs.getString("shelflocation"));
                storeproduct.setReorderPoint(rs.getInt("reorderpoint"));
                storeproduct.setQtyToOrder(rs.getInt("qtytoorder"));
                storeproduct.setQtyOnOrder(rs.getInt("qtyonorder")); 
                // Sets already in database
                storeproduct.setObjectAlreadyInDB(true);
                
                // adds to cache
                cache.put(storeproduct.getId(), storeproduct);
                
            }else{
                throw new DataException("Object was not found in the database.");
            }
        }
        
        
        // returns object
        return storeproduct;
    }
    
    
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     *
     * @par StoreProductBO storeproduct
     * @throws DataExcpetion
     */
    public synchronized void save(StoreProductBO storeproduct) throws DataException {
        try {
            
            
            // retrieve a database connection from the pool
            ConnectionPool connPool = ConnectionPool.getInstance();
            
            Connection conn = connPool.get();
            
            
            try {
                
                // Saves storeproduct
                save(storeproduct, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not save record for" + storeproduct.getId(), e);
            }
            
            // After all items are saved the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            // touch the cache for the object
            Cache.getInstance().touch(storeproduct.getId());
            
            
            
            
        } catch (DataException e) {
            throw e;
            
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not save record for" + storeproduct.getId(), e);
        } catch (SQLException e) {
            throw new DataException("Could not save record for" + storeproduct.getId(), e);
        }
        
        
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     *
     *  @par: StoreProductBO Connection conn
     *  @throws: SQLException, DataException
     */
    synchronized void save(StoreProductBO storeproduct, Connection conn) throws SQLException, DataException {
        
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (storeproduct.isDirty()) {
            if (storeproduct.isObjectAlreadyInDB()) {
                    update(storeproduct, conn);
            }else{
                insert(storeproduct, conn);            
            }
            storeproduct.setDirty(false);
           
        }
        
        
        
        
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par StoreProductBO
     */
    private synchronized void update(StoreProductBO storeproduct, Connection conn) throws SQLException, DataException {
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE store_product SET qtyonhand = ?, shelflocation = ?, reorderpoint = ?, qtytoorder = ?, qtyonorder = ? WHERE store_id = ? AND product_id = ?");
        pstmt.setInt(1, storeproduct.getQtyOnHand());
        pstmt.setString(2, storeproduct.getShelfLocation());
        pstmt.setInt(3, storeproduct.getReorderPoint());
        pstmt.setInt(4, storeproduct.getQtyToOrder());
        pstmt.setInt(5, storeproduct.getQtyOnOrder());
        pstmt.setString(6, storeproduct.getStoreId());
        pstmt.setString(7, storeproduct.getProductId());
        pstmt.execute();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par CustomerBO customer
     */
    private synchronized void insert(StoreProductBO storeproduct, Connection conn) throws SQLException, DataException {
        
        // Creates prepared statemetn
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO store_product VALUES(?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, storeproduct.getStoreId());
        pstmt.setString(2, storeproduct.getProductId());
        pstmt.setInt(3, storeproduct.getQtyOnHand());
        pstmt.setString(4, storeproduct.getShelfLocation());
        pstmt.setInt(5, storeproduct.getReorderPoint());
        pstmt.setInt(6, storeproduct.getQtyToOrder());
        pstmt.setInt(7, storeproduct.getQtyOnOrder());
        pstmt.execute();
        
        //Sets already in database true
        storeproduct.setObjectAlreadyInDB(true);
        
    }
    
    
    /* storeproducts cannot be deleted. They an only be set to void if not finished */
    
    public synchronized void delete(StoreProductBO line)throws DataException{
        
        throw new DataException("You can not delete a storeproduct. You must void the storeproduct");
        /* storeproducts cannot be deleted. They an only be set to void if not finished */
    }
    
    
    
    
    /**
     *  This method reads all storeproducts lines for a customer
     *
     *  @Par: String: id, Connection conn
     *  @Return: List<StoreProductBO>
     *  @throws: DataException, SQLException
     */
    synchronized List readBystoreproduct(String id, Connection conn) throws SQLException, DataException {
        
        List storeproducts = new LinkedList();
        
        // prepares statemnt
        
        PreparedStatement pstmt = conn.prepareStatement("SELECT storeproduct_id FROM storeproducts WHERE customer = ?");
        pstmt.setString(1, id);
        pstmt.execute();
        ResultSet rs = pstmt.getResultSet();
        
        
        while(rs.next()){
            String line_id = rs.getString("storeproduct_id");
            storeproducts.add(read(line_id, conn));
        }
        
        return storeproducts;
    }

    public List getProductsToOrder(List listOfProducts) {
        //Connection conn = ConnectionPool.getInstance().get();
        List productsToOrder = new LinkedList();
        //here i just go through all the products, and if (QOH + QOO) < ROP, add them to a list to order more
        for(int i = 0; i<listOfProducts.size(); i++){
            StoreProductBO prod = (StoreProductBO) listOfProducts.get(i);
            if((prod.getQtyOnHand() + prod.getQtyOnOrder()) < prod.getReorderPoint()){
                productsToOrder.add(prod);
            }
            
        }//for
        return productsToOrder;
    }
    
}
